﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using E4RPTools;
using ZecaFil.DataSet;

namespace ZecaFil
{
    public class Database
    {
        public static ClienteDataSet GetClients(SqlTransaction sqlTransaction)
        {
            var dataSet = new ClienteDataSet();
            const string query =
                @"  select	Cliente,
                            Nome,
                            Fac_Local as Localidade,
                            CDU_Contacto as Contacto
		            from	Clientes
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Cliente, query);
            return dataSet;
        }

        public static TecnicoDataSet GetTechnicals(SqlTransaction sqlTransaction)
        {
            var dataSet = new TecnicoDataSet();
            const string query =
                @"  select	Tecnico,
                            Nome
		            from	Tecnicos
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Tecnico, query);
            return dataSet;
        }

        public static VendedorDataSet GetSalesmans(SqlTransaction sqlTransaction)
        {
            var dataSet = new VendedorDataSet();
            const string query =
                @"  select	v.Vendedor,
                            v.Nome,
                            v.Localidade,
                            v.Morada,
		                    v.Telemovel,
		                    f.Foto
                    from	Vendedores v left join VendedorFoto f on f.vendedor = v.Vendedor
                    order by v.Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Vendedor, query);
            return dataSet;
        }

        public static RelatorioDataSet GetReports(SqlTransaction sqlTransaction)
        {
            var dataSet = new RelatorioDataSet();
            const string query =
                @"  select	CDU_Nome as Nome,
                            CDU_Ficheiro as Ficheiro
		            from	TDU_Relatorio
                    order by CDU_Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Relatorio, query);
            return dataSet;
        }

        public static PerfilUtilizadorDataSet GetProfiles()
        {
            var dataSet = new PerfilUtilizadorDataSet();
            dataSet.Perfil.AddPerfilRow((int)UserProfile.Administrator, "Administrador");
            dataSet.Perfil.AddPerfilRow((int)UserProfile.Operator, "Operador");
            return dataSet;
        }

        #region User

        private static long GetUserId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(CDU_Id)
                    from    TDU_Utilizador";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertUser(SqlTransaction sqlTransaction, UtilizadorDataSet.UtilizadorRow user)
        {
            const string query =
                @"  insert 
                    into    TDU_Utilizador(CDU_Nome, CDU_Password, CDU_Perfil, CDU_Zona, CDU_ExecutaIntervencao, CDU_ExecutaVenda) 
                    values  (@Nome, @Password, @Perfil, @Zona, @ExecutaIntervencao, @ExecutaVenda)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Nome", user.Nome),
                                        new SqlParameter("Password", user.Password),
                                        new SqlParameter("Perfil", user.Perfil),
                                        new SqlParameter("Zona", user.IsZonaNull() ? Convert.DBNull : user.Zona),
                                        new SqlParameter("ExecutaIntervencao", user.ExecutaIntervencao),
                                        new SqlParameter("ExecutaVenda", user.ExecutaVenda)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                user.Id = GetUserId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateUser(SqlTransaction sqlTransaction, UtilizadorDataSet.UtilizadorRow user)
        {
            const string query =
                @"  update  TDU_Utilizador
                    set     CDU_Nome = @Nome,
                            CDU_Password = @Password,
                            CDU_Perfil = @Perfil,
                            CDU_Zona = @Zona,
                            CDU_ExecutaIntervencao = @ExecutaIntervencao,
                            CDU_ExecutaVenda = @ExecutaVenda
                    where   CDU_Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", user.Id),
                                        new SqlParameter("Nome", user.Nome),
                                        new SqlParameter("Password", user.Password),
                                        new SqlParameter("Perfil", user.Perfil),
                                        new SqlParameter("Zona", user.IsZonaNull() ? Convert.DBNull : user.Zona),
                                        new SqlParameter("ExecutaIntervencao", user.ExecutaIntervencao),
                                        new SqlParameter("ExecutaVenda", user.ExecutaVenda)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }


        public static UtilizadorDataSet GetUsers(SqlTransaction sqlTransaction)
        {
            var dataSet = new UtilizadorDataSet();
            const string query =
                @"  select	CDU_Id as Id, CDU_Nome as Nome, CDU_Password as Password, CDU_Perfil as Perfil, CDU_Zona as Zona, 
                            CDU_ExecutaIntervencao  as ExecutaIntervencao, CDU_ExecutaVenda as ExecutaVenda
                    from	TDU_Utilizador
                    order by CDU_Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Utilizador, query);
            return dataSet;
        }

        public static UtilizadorDataSet GetUser(SqlTransaction sqlTransaction, long userId)
        {
            var dataSet = new UtilizadorDataSet();
            const string query =
                @"  select	CDU_Id as Id, CDU_Nome as Nome, CDU_Password as Password, CDU_Perfil as Perfil, CDU_Zona as Zona, 
                            CDU_ExecutaIntervencao  as ExecutaIntervencao, CDU_ExecutaVenda as ExecutaVenda
                    from	TDU_Utilizador
                    where   CDU_Id = @Id";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Utilizador, query, new SqlParameter("Id", userId));
            return dataSet;
        }

        public static void SaveUser(SqlTransaction sqlTransaction, UtilizadorDataSet.UtilizadorRow user)
        {
            if (user.RowState == DataRowState.Added)
                InsertUser(sqlTransaction, user);
            else
                UpdateUser(sqlTransaction, user);
        }

        #endregion

        #region ServiceType

        private static void InsertServiceType(SqlTransaction sqlTransaction, TipoServicoDataSet.TipoServicoRow serviceType)
        {
            const string query =
                @"  insert 
                    into    TDU_TiposServico(CDU_Codigo, CDU_Descricao) 
                    values  (@Codigo, @Descricao)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Codigo", serviceType.Codigo),
                                        new SqlParameter("Descricao", serviceType.Descricao)
                                    };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void UpdateServiceType(SqlTransaction sqlTransaction, TipoServicoDataSet.TipoServicoRow serviceType)
        {
            const string query =
                @"  update  TDU_TiposServico
                    set     CDU_Descricao = @Descricao
                    where   CDU_Codigo = @Codigo";
            var sqlParameters = new List<SqlParameter>
                                    {
                                       new SqlParameter("Codigo", serviceType.Codigo),
                                        new SqlParameter("Descricao", serviceType.Descricao)
                                    };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }


        public static TipoServicoDataSet GetServiceTypes(SqlTransaction sqlTransaction)
        {
            var dataSet = new TipoServicoDataSet();
            const string query =
                @"  select	CDU_Codigo as Codigo,
                            CDU_Descricao as Descricao
		            from	TDU_TiposServico
                    order by CDU_Codigo";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TipoServico, query);
            return dataSet;
        }

        public static TipoServicoDataSet GetServiceType(SqlTransaction sqlTransaction, string code)
        {
            var dataSet = new TipoServicoDataSet();
            const string query =
                @"  select	CDU_Codigo as Codigo,
                            CDU_Descricao as Descricao
		            from	TDU_TiposServico
                    where   upper(CDU_Codigo) = upper(@Codigo)";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TipoServico, query, new SqlParameter("Codigo", code));
            return dataSet;
        }

        public static void SaveServiceType(SqlTransaction sqlTransaction, TipoServicoDataSet.TipoServicoRow serviceTpe)
        {
            if (serviceTpe.RowState == DataRowState.Added)
                InsertServiceType(sqlTransaction, serviceTpe);
            else
                UpdateServiceType(sqlTransaction, serviceTpe);
        }

        #endregion

        #region ServiceDesignation

        private static void InsertServiceDesignation(SqlTransaction sqlTransaction, DesignacaoServicoDataSet.DesignacaoServicoRow serviceDesignation)
        {
            const string query =
                @"  insert 
                    into    TDU_DesignacaoServico(CDU_Codigo, CDU_Descricao) 
                    values  (@Codigo, @Descricao)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Codigo", serviceDesignation.Codigo),
                                        new SqlParameter("Descricao", serviceDesignation.Descricao)
                                    };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void UpdateServiceDesignation(SqlTransaction sqlTransaction, DesignacaoServicoDataSet.DesignacaoServicoRow serviceDesignation)
        {
            const string query =
                @"  update  TDU_DesignacaoServico
                    set     CDU_Descricao = @Descricao
                    where   CDU_Codigo = @Codigo";
            var sqlParameters = new List<SqlParameter>
                                    {
                                       new SqlParameter("Codigo", serviceDesignation.Codigo),
                                        new SqlParameter("Descricao", serviceDesignation.Descricao)
                                    };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }


        public static DesignacaoServicoDataSet GetServiceDesignations(SqlTransaction sqlTransaction)
        {
            var dataSet = new DesignacaoServicoDataSet();
            const string query =
                @"  select	CDU_Codigo as Codigo,
                            CDU_Descricao as Descricao
		            from	TDU_DesignacaoServico
                    order by CDU_Codigo";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.DesignacaoServico, query);
            return dataSet;
        }

        public static DesignacaoServicoDataSet GetServiceDesignation(SqlTransaction sqlTransaction, string code)
        {
            var dataSet = new DesignacaoServicoDataSet();
            const string query =
                @"  select	CDU_Codigo as Codigo,
                            CDU_Descricao as Descricao
		            from	TDU_DesignacaoServico
                    where   upper(CDU_Codigo) = upper(@Codigo)";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.DesignacaoServico, query, new SqlParameter("Codigo", code));
            return dataSet;
        }

        public static void SaveServiceDesignation(SqlTransaction sqlTransaction, DesignacaoServicoDataSet.DesignacaoServicoRow serviceDesignation)
        {
            if (serviceDesignation.RowState == DataRowState.Added)
                InsertServiceDesignation(sqlTransaction, serviceDesignation);
            else
                UpdateServiceDesignation(sqlTransaction, serviceDesignation);
        }

        #endregion

        #region ContractType

        private static void InsertContractType(SqlTransaction sqlTransaction, TipoContractoDataSet.TipoContractoRow contractType)
        {
            const string query =
                @"  insert 
                    into    TDU_TiposContracto(CDU_Codigo, CDU_Descricao) 
                    values  (@Codigo, @Descricao)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Codigo", contractType.Codigo),
                                        new SqlParameter("Descricao", contractType.Descricao)
                                    };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void UpdateContractType(SqlTransaction sqlTransaction, TipoContractoDataSet.TipoContractoRow contractType)
        {
            const string query =
                @"  update  TDU_TiposContracto
                    set     CDU_Descricao = @Descricao
                    where   CDU_Codigo = @Codigo";
            var sqlParameters = new List<SqlParameter>
                                    {
                                       new SqlParameter("Codigo", contractType.Codigo),
                                        new SqlParameter("Descricao", contractType.Descricao)
                                    };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }


        public static TipoContractoDataSet GetContractTypes(SqlTransaction sqlTransaction)
        {
            var dataSet = new TipoContractoDataSet();
            const string query =
                @"  select	CDU_Codigo as Codigo,
                            CDU_Descricao as Descricao
		            from	TDU_TiposContracto
                    order by CDU_Codigo";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TipoContracto, query);
            return dataSet;
        }

        public static TipoContractoDataSet GetContractType(SqlTransaction sqlTransaction, string code)
        {
            var dataSet = new TipoContractoDataSet();
            const string query =
                @"  select	CDU_Codigo as Codigo,
                            CDU_Descricao as Descricao
		            from	TDU_TiposContracto
                    where   upper(CDU_Codigo) = upper(@Codigo)";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TipoContracto, query, new SqlParameter("Codigo", code));
            return dataSet;
        }

        public static void SaveContractType(SqlTransaction sqlTransaction, TipoContractoDataSet.TipoContractoRow contractType)
        {
            if (contractType.RowState == DataRowState.Added)
                InsertContractType(sqlTransaction, contractType);
            else
                UpdateContractType(sqlTransaction, contractType);
        }

        #endregion

        #region Sale

        private static long GetSaleId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(CDU_Id)
                    from    TDU_Venda";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertSale(SqlTransaction sqlTransaction, VendaDataSet.VendaRow sale)
        {
            const string query =
                @"  insert 
                    into    TDU_Venda(CDU_Vendedor, CDU_Zona, CDU_Morada, CDU_Contacto) 
                    values  (@Vendedor, @Zona, @Morada, @Contacto)";            
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Vendedor", sale.Vendedor),
                                        new SqlParameter("Zona", sale.IsZonaNull() ? Convert.DBNull : sale.Zona),
                                        new SqlParameter("Morada", sale.IsMoradaNull() ? Convert.DBNull : sale.Morada),
                                        new SqlParameter("Contacto", sale.IsContactoNull() ? Convert.DBNull : sale.Contacto)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                sale.Id = GetSaleId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateSale(SqlTransaction sqlTransaction, VendaDataSet.VendaRow sale)
        {
            const string query =
                @"  update  TDU_Venda
                    set     CDU_Zona = @Zona,
                            CDU_Morada = @Morada,
                            CDU_Contacto = @Contacto
                    where   CDU_Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                       new SqlParameter("Id", sale.Id),
                                        new SqlParameter("Zona", sale.IsZonaNull() ? Convert.DBNull : sale.Zona),
                                        new SqlParameter("Morada", sale.IsMoradaNull() ? Convert.DBNull : sale.Morada),
                                        new SqlParameter("Contacto", sale.IsContactoNull() ? Convert.DBNull : sale.Contacto)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }


        private static long GetSaleItemId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(CDU_IdItem)
                    from    TDU_ItemVenda";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertSaleItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, VendaDataSet.ItemVendaRow saleItem)
        {
            const string query =
                @"  insert 
                    into    TDU_ItemVenda(CDU_Id, CDU_Data, CDU_DataRetirada, CDU_Cliente, CDU_DesignacaoServico, CDU_Quantidade, CDU_Valor, CDU_TipoContracto, CDU_Anulado, CDU_ValidadeContrato) 
                    values  (@Id, @Data, @DataRetirada, @Cliente, @DesignacaoServico, @Quantidade, @Valor, @TipoContracto, @Anulado, @ValidadeContrato)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", saleItem.VendaRow.Id),
                                        new SqlParameter("Data", saleItem.Data),
                                        new SqlParameter("DataRetirada", saleItem.IsDataRetiradaNull()? Convert.DBNull : saleItem.DataRetirada),
                                        new SqlParameter("Cliente", saleItem.Cliente),
                                        new SqlParameter("DesignacaoServico", saleItem.IsDesignacaoServicoNull()? Convert.DBNull : saleItem.DesignacaoServico),
                                        new SqlParameter("Quantidade", saleItem.Quantidade),
                                        new SqlParameter("Valor", saleItem.Valor),
                                        new SqlParameter("TipoContracto", saleItem.IsTipoContratoNull()? Convert.DBNull : saleItem.TipoContrato),
                                        new SqlParameter("Anulado", saleItem.Anulado),
                                        new SqlParameter("ValidadeContrato", saleItem.IsValidadeContratoNull()? Convert.DBNull : saleItem.ValidadeContrato)
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
            saleItem.IdItem = GetSaleItemId(sqlDataAccess, sqlTransaction);
        }

        private static void UpdateSaleItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, VendaDataSet.ItemVendaRow saleItem)
        {
            const string query =
                @"  update  TDU_ItemVenda
                    set     CDU_Data = @Data, 
                            CDU_DataRetirada = @DataRetirada, 
                            CDU_Cliente = @Cliente, 
                            CDU_DesignacaoServico = @DesignacaoServico, 
                            CDU_Quantidade = @Quantidade, 
                            CDU_Valor = @Valor,
                            CDU_TipoContracto = @TipoContracto,
                            CDU_Anulado = @Anulado,
                            CDU_ValidadeContrato = @ValidadeContrato
                    where   CDU_IdItem = @IdItem";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("IdItem", saleItem.IdItem),
                                        new SqlParameter("Data", saleItem.Data),
                                        new SqlParameter("DataRetirada", saleItem.IsDataRetiradaNull()? Convert.DBNull : saleItem.DataRetirada),
                                        new SqlParameter("Cliente", saleItem.Cliente),
                                        new SqlParameter("DesignacaoServico", saleItem.IsDesignacaoServicoNull()? Convert.DBNull : saleItem.DesignacaoServico),
                                        new SqlParameter("Quantidade", saleItem.Quantidade),
                                        new SqlParameter("Valor", saleItem.Valor),
                                        new SqlParameter("TipoContracto", saleItem.IsTipoContratoNull()? Convert.DBNull : saleItem.TipoContrato),
                                        new SqlParameter("Anulado", saleItem.Anulado),
                                        new SqlParameter("ValidadeContrato", saleItem.IsValidadeContratoNull()? Convert.DBNull : saleItem.ValidadeContrato)
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void DeleteSaleItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, DataRow saleItem)
        {
            const string query =
                @"  delete  TDU_ItemVenda
                    where   CDU_IdItem = @IdItem";
            var saleItemId = Convert.ToInt64(saleItem["IdItem", DataRowVersion.Original]);
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("IdItem", saleItemId));
        }


        public static VendasDataSet GetSales(SqlTransaction sqlTransaction, string zone)
        {
            var dataSet = new VendasDataSet();
            var query =
                @"  select	v.CDU_Id as Id,
                            s.Nome as Vendedor,
                            v.CDU_Zona as Zona,
                            v.CDU_Morada as Morada,
                            v.CDU_Contacto as Contacto,
                            i.CDU_IdItem as IdItem,
                            i.CDU_Data as Data,
                            i.CDU_DataRetirada as DataRetirada,
                            c.Nome as Cliente,
                            ds.CDU_Descricao as DesignacaoServico, 
                            i.CDU_Quantidade as Quantidade, 
                            i.CDU_Valor as Valor,
                            tc.CDU_Descricao as TipoContrato,
                            CDU_Anulado as Anulado,
                            i.CDU_ValidadeContrato as ValidadeContrato
		            from	TDU_Venda v, TDU_ItemVenda i
                            left join TDU_DesignacaoServico ds on ds.CDU_Codigo = i.CDU_DesignacaoServico
                            left join TDU_TiposContracto tc on tc.CDU_Codigo = i.CDU_TipoContracto,
                            Vendedores s, Clientes c
                    where   i.CDU_Id = v.CDU_Id
                            and s.Vendedor = v.CDU_Vendedor
                            and c.Cliente = i.CDU_Cliente";
            if (zone != "")
                query += " and upper(v.CDU_Zona) = '" + zone.ToUpper() + "'";            
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Venda, query);
            return dataSet;
        }

        public static VendaDataSet GetSale(SqlTransaction sqlTransaction, long id)
        {
            var dataSet = new VendaDataSet();
            const string saleQuery =
                @"  select	CDU_Id as Id,
                            CDU_Vendedor as Vendedor,
                            CDU_Zona as Zona,
                            CDU_Morada as Morada,
                            CDU_Contacto as Contacto
		            from	TDU_Venda
                    where   CDU_Id = @Id";
            const string saleItemQuery =
                @"  select	CDU_Id as Id,
                            CDU_IdItem as IdItem,
                            CDU_Data as Data,
                            CDU_DataRetirada as DataRetirada,
                            CDU_Cliente as Cliente,                            
                            CDU_DesignacaoServico as DesignacaoServico, 
                            CDU_Quantidade as Quantidade, 
                            CDU_Valor as Valor,
                            CDU_TipoContracto as TipoContrato,
                            CDU_Anulado as Anulado,
                            CDU_ValidadeContrato as ValidadeContrato
		            from	TDU_ItemVenda
                    where   CDU_Id = @Id";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Venda, saleQuery, new SqlParameter("Id", id));
                dataAccess.Fill(sqlTransaction, dataSet.ItemVenda, saleItemQuery, new SqlParameter("Id", id));
            }
            return dataSet;
        }

        public static void SaveSale(SqlTransaction sqlTransaction, VendaDataSet.VendaRow sale)
        {
            if (sale.RowState == DataRowState.Added)
                InsertSale(sqlTransaction, sale);
            else
                UpdateSale(sqlTransaction, sale);
        }

        public static void UpdateSaleItems(SqlTransaction sqlTransaction, VendaDataSet.ItemVendaDataTable saleItems)
        {
            using (var sqlDataAccess = new SqlDataAccess())
            {
                foreach (VendaDataSet.ItemVendaRow saleItem in saleItems)
                {
                    switch (saleItem.RowState)
                    {
                        case DataRowState.Added:
                            InsertSaleItem(sqlDataAccess, sqlTransaction, saleItem);
                            break;
                        case DataRowState.Modified:
                            UpdateSaleItem(sqlDataAccess, sqlTransaction, saleItem);
                            break;
                        case DataRowState.Deleted:
                            DeleteSaleItem(sqlDataAccess, sqlTransaction, saleItem);
                            break;
                    }
                }
            }
        }

        public static VendaDataSet GetSalePrint(SqlTransaction sqlTransaction, long id)
        {
            var dataSet = new VendaDataSet();
            const string saleQuery =
                @"  select	v.CDU_Id as Id,
                            s.Nome as Vendedor,
                            v.CDU_Zona as Zona,
                            v.CDU_Morada as Morada,
                            v.CDU_Contacto as Contacto,
                            s.Telemovel as VendedorLocalidade,
                            f.Foto as VendedorFoto
		            from	TDU_Venda v, 
                            Vendedores s left join VendedorFoto f on f.vendedor = s.Vendedor
                    where   CDU_Id = @Id
                            and s.Vendedor = v.CDU_Vendedor";
            const string saleItemQuery =
                @"  select	i.CDU_Id as Id,
                            i.CDU_IdItem as IdItem,
                            i.CDU_Data as Data,
                            i.CDU_DataRetirada as DataRetirada,
                            c.Nome as Cliente,                            
                            ds.CDU_Descricao as DesignacaoServico, 
                            i.CDU_Quantidade as Quantidade, 
                            i.CDU_Valor as Valor,
                            tc.CDU_Descricao as TipoContrato,
                            i.CDU_Anulado as Anulado,
                            c.Fac_Local as ClienteLocalidade,
                            c.CDU_Contacto as ClienteContacto,
                            i.CDU_ValidadeContrato as ValidadeContrato
		            from	TDU_ItemVenda i
                            left join TDU_DesignacaoServico ds on ds.CDU_Codigo = i.CDU_DesignacaoServico
                            left join TDU_TiposContracto tc on tc.CDU_Codigo = i.CDU_TipoContracto,
                            Clientes c 
                    where   CDU_Id = @Id
                            and c.Cliente = i.CDU_Cliente";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Venda, saleQuery, new SqlParameter("Id", id));
                dataAccess.Fill(sqlTransaction, dataSet.ItemVenda, saleItemQuery, new SqlParameter("Id", id));
            }
            return dataSet;
        }

        #endregion

        #region WorkOrder

        private static int GetLastWorkOrder(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, int year)
        {
            const string query =
                @"  select  max(CDU_Numero)
                    from    TDU_Intervencao
                    where   year(CDU_Data) = @Ano";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Ano", year));
        }

        private static long GetWorkOrderId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(CDU_Id)
                    from    TDU_Intervencao";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertWorkOrder(SqlTransaction sqlTransaction, IntervencaoDataSet.IntervencaoRow workOrder)
        {
            using (var sqlDataAccess = new SqlDataAccess())
                workOrder.Numero = GetLastWorkOrder(sqlDataAccess, sqlTransaction, workOrder.Data.Year) + 1;
            workOrder.Nome = workOrder.Data.Year + "/" + workOrder.Numero.ToString().PadLeft(5, '0');
            const string query =
                @"  insert 
                    into    TDU_Intervencao(CDU_Data, CDU_Numero, CDU_Nome, CDU_Tecnico, CDU_Zona) 
                    values  (@Data, @Numero, @Nome, @Tecnico, @Zona)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Data", workOrder.Data),
                                        new SqlParameter("Numero", workOrder.Numero),
                                        new SqlParameter("Nome", workOrder.Nome),
                                        new SqlParameter("Tecnico", workOrder.Tecnico),
                                        new SqlParameter("Zona", workOrder.IsZonaNull() ? Convert.DBNull : workOrder.Zona)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                workOrder.Id = GetWorkOrderId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateWorkOrder(SqlTransaction sqlTransaction, IntervencaoDataSet.IntervencaoRow workOrder)
        {
            const string query =
                @"  update  TDU_Intervencao
                    set     CDU_Tecnico = @Tecnico,
                            CDU_Zona = @Zona
                    where   CDU_Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", workOrder.Id),
                                        new SqlParameter("Tecnico", workOrder.Tecnico),
                                        new SqlParameter("Zona", workOrder.IsZonaNull() ? Convert.DBNull : workOrder.Zona)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }


        private static long GetWorkOrderItemId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(CDU_IdItem)
                    from    TDU_ItemIntervencao";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertWorkOrderItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, IntervencaoDataSet.ItemIntervencaoRow workOrderItem)
        {
            const string query =
                @"  insert 
                    into    TDU_ItemIntervencao(CDU_Id, CDU_Data, CDU_TipoServico, CDU_Cliente, CDU_Contacto, CDU_Localidade, CDU_Executado, CDU_Reclamacao, CDU_Factura, CDU_Valor, CDU_Notas, CDU_Pago) 
                    values  (@Id, @Data, @TipoServico, @Cliente, @Contacto, @Localidade, @Executado, @Reclamacao, @Factura, @Valor, @Notas, @Pago)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", workOrderItem.IntervencaoRow.Id),
                                        new SqlParameter("Data", workOrderItem.Data),
                                        new SqlParameter("TipoServico", workOrderItem.IsTipoServicoNull()? Convert.DBNull : workOrderItem.TipoServico),
                                        new SqlParameter("Cliente", workOrderItem.Cliente),
                                        new SqlParameter("Contacto", workOrderItem.IsContactoNull()? Convert.DBNull : workOrderItem.Contacto),
                                        new SqlParameter("Localidade", workOrderItem.IsLocalidadeNull() ? Convert.DBNull : workOrderItem.Localidade),
                                        new SqlParameter("Executado", workOrderItem.Executado),
                                        new SqlParameter("Reclamacao", workOrderItem.Reclamacao),
                                        new SqlParameter("Factura", workOrderItem.IsFacturaNull() ? Convert.DBNull : workOrderItem.Factura),
                                        new SqlParameter("Valor", workOrderItem.Valor),
                                        new SqlParameter("Notas", workOrderItem.IsNotasNull() ? Convert.DBNull : workOrderItem.Notas),
                                        new SqlParameter("Pago", workOrderItem.Pago)
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
            workOrderItem.IdItem = GetWorkOrderItemId(sqlDataAccess, sqlTransaction);
        }

        private static void UpdateWorkOrderItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, IntervencaoDataSet.ItemIntervencaoRow workOrderItem)
        {
            const string query =
                @"  update  TDU_ItemIntervencao
                    set     CDU_Data = @Data, 
                            CDU_TipoServico = @TipoServico, 
                            CDU_Cliente = @Cliente, 
                            CDU_Contacto = @Contacto, 
                            CDU_Localidade = @Localidade, 
                            CDU_Executado = @Executado,
                            CDU_Reclamacao = @Reclamacao,
                            CDU_Factura = @Factura,
                            CDU_Valor = @Valor,
                            CDU_Notas = @Notas,
                            CDU_Pago = @Pago
                    where   CDU_IdItem = @IdItem";            
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("IdItem", workOrderItem.IdItem),
                                        new SqlParameter("Data", workOrderItem.Data),
                                        new SqlParameter("TipoServico", workOrderItem.IsTipoServicoNull()? Convert.DBNull : workOrderItem.TipoServico),
                                        new SqlParameter("Cliente", workOrderItem.Cliente),
                                        new SqlParameter("Contacto", workOrderItem.IsContactoNull()? Convert.DBNull : workOrderItem.Contacto),
                                        new SqlParameter("Localidade", workOrderItem.IsLocalidadeNull() ? Convert.DBNull : workOrderItem.Localidade),
                                        new SqlParameter("Executado", workOrderItem.Executado),
                                        new SqlParameter("Reclamacao", workOrderItem.Reclamacao),
                                        new SqlParameter("Factura", workOrderItem.IsFacturaNull() ? Convert.DBNull : workOrderItem.Factura),
                                        new SqlParameter("Valor", workOrderItem.Valor),
                                        new SqlParameter("Notas", workOrderItem.IsNotasNull() ? Convert.DBNull : workOrderItem.Notas),
                                        new SqlParameter("Pago", workOrderItem.Pago)
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void DeleteWorkOrderItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, DataRow saleItem)
        {
            const string query =
                @"  delete  TDU_ItemIntervencao
                    where   CDU_IdItem = @IdItem";
            var saleItemId = Convert.ToInt64(saleItem["IdItem", DataRowVersion.Original]);
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("IdItem", saleItemId));
        }


        public static IntervencoesDataSet GetWorkOrders(SqlTransaction sqlTransaction, string zone)
        {
            var dataSet = new IntervencoesDataSet();
            var query =
                @"  select	i.CDU_Id as Id,
                            i.CDU_Nome as Nome,
                            t.Nome as Tecnico,
                            i.CDU_Zona as Zona,
                            ii.CDU_IdItem as IdItem,
                            ii.CDU_Data as Data,
                            ts.CDU_Descricao as TipoServico,
                            c.Nome as Cliente,                            
                            ii.CDU_Contacto as Contacto, 
                            ii.CDU_Localidade as Localidade, 
                            ii.CDU_Executado as Executado, 
                            ii.CDU_Reclamacao as Reclamacao,
                            1 as Marcado,
                            ii.CDU_Executado as Produtividade,  
                            ii.CDU_Factura as Factura, 
                            ii.CDU_Valor as Valor,
                            ii.CDU_Notas as Notas,
                            ii.CDU_Pago as Pago,
                            case ii.CDU_Pago when 1 then ii.CDU_Valor else 0 end as ValorPago        
		            from	TDU_Intervencao i, Tecnicos t, TDU_ItemIntervencao ii, Clientes c, TDU_TiposServico ts 
                    where   ii.CDU_Id = i.CDU_Id
                            and t.Tecnico = i.CDU_Tecnico
                            and c.Cliente = ii.CDU_Cliente
                            and ts.CDU_Codigo = ii.CDU_TipoServico";
            if (zone != "")
                query += " and upper(i.CDU_Zona) = '" + zone.ToUpper() + "'";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Intervencao, query);
            return dataSet;
        }

        public static IntervencaoDataSet GetWorkOrder(SqlTransaction sqlTransaction, long id)
        {
            var dataSet = new IntervencaoDataSet();
            const string workOrderQuery =
                @"  select	CDU_Id as Id,
                            CDU_Data as Data,
                            CDU_Numero as Numero,
                            CDU_Nome as Nome,
                            CDU_Tecnico as Tecnico,
                            CDU_Zona as Zona
		            from	TDU_Intervencao
                    where   CDU_Id = @Id";
            const string saleItemQuery =
                @"  select	CDU_Id as Id,
                            CDU_IdItem as IdItem,
                            CDU_Data as Data,
                            CDU_TipoServico as TipoServico,
                            CDU_Cliente as Cliente,                            
                            CDU_Contacto as Contacto, 
                            CDU_Localidade as Localidade, 
                            CDU_Executado as Executado, 
                            CDU_Reclamacao as Reclamacao, 
                            CDU_Factura as Factura, 
                            CDU_Valor as Valor,
                            CDU_Notas as Notas,
                            CDU_Pago as Pago
		            from	TDU_ItemIntervencao
                    where   CDU_Id = @Id";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Intervencao, workOrderQuery, new SqlParameter("Id", id));
                dataAccess.Fill(sqlTransaction, dataSet.ItemIntervencao, saleItemQuery, new SqlParameter("Id", id));
            }
            return dataSet;
        }

        public static void SaveWorkOrder(SqlTransaction sqlTransaction, IntervencaoDataSet.IntervencaoRow workOrder)
        {
            if (workOrder.RowState == DataRowState.Added)
                InsertWorkOrder(sqlTransaction, workOrder);
            else
                UpdateWorkOrder(sqlTransaction, workOrder);
        }

        public static void UpdateWorkOrderItems(SqlTransaction sqlTransaction, IntervencaoDataSet.ItemIntervencaoDataTable workOrderItems)
        {
            using (var sqlDataAccess = new SqlDataAccess())
            {
                foreach (IntervencaoDataSet.ItemIntervencaoRow workOrderItem in workOrderItems)
                {
                    switch (workOrderItem.RowState)
                    {
                        case DataRowState.Added:
                            InsertWorkOrderItem(sqlDataAccess, sqlTransaction, workOrderItem);
                            break;
                        case DataRowState.Modified:
                            UpdateWorkOrderItem(sqlDataAccess, sqlTransaction, workOrderItem);
                            break;
                        case DataRowState.Deleted:
                            DeleteWorkOrderItem(sqlDataAccess, sqlTransaction, workOrderItem);
                            break;
                    }
                }
            }
        }

        public static IntervencaoDataSet GetWorkOrderPrint(SqlTransaction sqlTransaction, long id)
        {
            var dataSet = new IntervencaoDataSet();
            const string workOrderQuery =
                @"  select	CDU_Id as Id,
                            CDU_Data as Data,
                            CDU_Numero as Numero,
                            CDU_Nome as Nome,
                            t.Nome as Tecnico,
                            i.CDU_Zona as Zona
		            from	TDU_Intervencao i, Tecnicos t
                    where   CDU_Id = @Id
                            and t.Tecnico = i.CDU_Tecnico";
            const string saleItemQuery =
                @"  select	CDU_Id as Id,
                            CDU_IdItem as IdItem,
                            CDU_Data as Data,
                            ts.CDU_Descricao as TipoServico,
                            c.Nome as Cliente,                            
                            i.CDU_Contacto as Contacto, 
                            CDU_Localidade as Localidade, 
                            CDU_Executado as Executado, 
                            CDU_Reclamacao as Reclamacao, 
                            CDU_Factura as Factura, 
                            i.CDU_Valor as Valor,
                            CDU_Notas as Notas,
                            c.Fac_Local as ClienteLocalidade,
                            i.CDU_Pago as Pago
		            from	TDU_ItemIntervencao i
                            left join TDU_TiposServico ts on ts.CDU_Codigo = i.CDU_TipoServico,
                            Clientes c
                    where   CDU_Id = @Id
                            and c.Cliente = i.CDU_Cliente";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Intervencao, workOrderQuery, new SqlParameter("Id", id));
                dataAccess.Fill(sqlTransaction, dataSet.ItemIntervencao, saleItemQuery, new SqlParameter("Id", id));
            }
            return dataSet;
        }

        #endregion

        #region Layout

        private static bool ExistsLayout(SqlTransaction sqlTransaction, string lista, string nome)
        {
            int count;
            const string query =
                @"  select	count(*)
		            from	TDU_ListaLayout
                    where   CDU_Lista = @Lista
                            and CDU_Nome = @Nome";
            using (var dataAccess = new SqlDataAccess())
                count = dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Lista", lista),
                    new SqlParameter("Nome", nome));
            return count > 0;
        }

        private static void InsertLayout(SqlTransaction sqlTransaction, string list, string name, string xmlLayout)
        {
            const string query =
                @"  insert 
                    into    TDU_ListaLayout(CDU_Lista, CDU_Nome, CDU_XmlLayout) 
                    values  (@List, @Name, @XmlLayout)";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("List", list),
                    new SqlParameter("Name", name), new SqlParameter("XmlLayout", xmlLayout));
        }

        private static void UpdateLayout(SqlTransaction sqlTransaction, string lista, string nome, string xmlLayout)
        {
            const string query =
                @"  update  TDU_ListaLayout 
                    set     CDU_XmlLayout = @XmlLayout
                    where   CDU_Lista = @Lista
                            and CDU_Nome = @Nome";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("Lista", lista),
                    new SqlParameter("Nome", nome), new SqlParameter("XmlLayout", xmlLayout));
        }


        public static ListaLayoutDataSet GetListLayouts(SqlTransaction sqlTransaction, string lista)
        {
            var dataSet = new ListaLayoutDataSet();
            const string query =
                @"  select	CDU_Lista as Lista, CDU_Nome as Nome, CDU_XmlLayout as XmlLayout
		            from	TDU_ListaLayout
                    where   CDU_Lista = @Lista
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.ListaLayout, query, new SqlParameter("Lista", lista));
            return dataSet;
        }

        public static void SaveLayout(SqlTransaction sqlTransaction, string lista, string nome, string xmlLayout)
        {
            if (ExistsLayout(sqlTransaction, lista, nome))
                UpdateLayout(sqlTransaction, lista, nome, xmlLayout);
            else
                InsertLayout(sqlTransaction, lista, nome, xmlLayout);

        }

        #endregion

        public static ZonaDataSet GetZones(string zone)
        {
            var dataSet = new ZonaDataSet();
            dataSet.Zona.AddZonaRow("");
            if (zone == "" || zone == "Castelo Branco") dataSet.Zona.AddZonaRow("Castelo Branco");
            if (zone == "" || zone == "Porto") dataSet.Zona.AddZonaRow("Porto");
            if (zone == "" || zone == "Lisboa") dataSet.Zona.AddZonaRow("Lisboa");
            return dataSet;
        }
    }
}
